﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
namespace md5Tools
{
    internal class MySQLHelper
    {
        private string connectionString;
        public MySQLHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }
        public MySQLHelper()
        {
            writeConnectionString(ConfigurationManager.AppSettings["mysqlLocalhost"], ConfigurationManager.AppSettings["mysqlDatabase"], ConfigurationManager.AppSettings["mysqlName"], ConfigurationManager.AppSettings["mysqlPwd"]);
        }

        /// <summary>
        /// 执行不返回结果集的SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 执行SQL语句并返回影响行数
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行一个查询，并返回结果集中第一行的第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 执行SQL查询并返回第一行第一列的值
                    return command.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行一个查询，并返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataAdapter和DataTable对象，并填充数据
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
            }
        }

        /// <summary>
        /// 执行一个查询，并将结果集映射到一个对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="selector"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(string sql, Func<IDataRecord, T> selector, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataReader对象并读取数据，将每行数据映射到对象并添加到列表中
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();
                        while (reader.Read())
                        {
                            list.Add(selector(reader));
                        }
                        return list;
                    }
                }
            }
        }
        /// <summary>
        /// 执行一个查询，并将结果映射到一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="selector"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteQueryEntity<T>(string sql, Func<IDataRecord, T> selector, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataReader对象并读取数据，将每行数据映射到对象并添加到列表中
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        T entity = default(T);
                        while (reader.Read())
                        {
                            entity = selector(reader);
                        }
                        return entity;
                    }
                }
            }
        }

        /// <summary>
        /// 向数据库中插入数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public int Insert(string tableName, Dictionary<string, object> data)
        {
            string[] columns = new string[data.Count];
            object[] values = new object[data.Count];

            int i = 0;
            foreach (KeyValuePair<string, object> item in data)
            {
                // 获取列名和值
                columns[i] = item.Key;
                values[i] = item.Value;
                i++;
            }

            string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, string.Join(",", columns), "@" + string.Join(",@", columns));

            // 将Dictionary转换为MySqlParameter数组，并执行SQL语句
            return ExecuteNonQuery(sql, ToMySqlParameters(data));
        }

        /// <summary>
        /// 更新数据库中的数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="data"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public int Update(string tableName, Dictionary<string, object> data, string whereClause = "")
        {
            string[] setValues = new string[data.Count];
            int i = 0;
            foreach (KeyValuePair<string, object> item in data)
            {
                // 获取列名和值
                setValues[i] = string.Format("{0}=@{0}", item.Key);
                i++;
            }

            string sql = string.Format("UPDATE {0} SET {1}", tableName, string.Join(",", setValues));

            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }

            // 将Dictionary转换为MySqlParameter数组，并执行SQL语句
            return ExecuteNonQuery(sql, ToMySqlParameters(data));
        }

        /// <summary>
        /// 删除数据库中的数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public int Delete(string tableName, string whereClause = "")
        {
            string sql = string.Format("DELETE FROM {0}", tableName);

            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }

            // 执行SQL语句并返回影响

            return ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 将Dictionary转换为MySqlParameter数组
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private MySqlParameter[] ToMySqlParameters(Dictionary<string, object> data)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            foreach (KeyValuePair<string, object> item in data)
            {
                parameters.Add(new MySqlParameter("@" + item.Key, item.Value));
            }

            return parameters.ToArray();
        }
        /// <summary>
        /// 写连接字符
        /// </summary>
        /// <param name="localhost"></param>
        /// <param name="myDatabase"></param>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        private void writeConnectionString(String localhost, String myDatabase, String name, String pwd)
        {
            connectionString = $"server={localhost};database={myDatabase};uid={name};password={pwd};";
        }
    }
}